package security.acl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

import security.db.DbMgr;
import security.db.DbQuery;
import security.msg.ErrorMessage;
import security.msg.InfoMessage;
import security.msg.Message;
import security.msg.ObjectMessage;
import util.CommonUtils;
import util.Constants;
import core.access.Read;
import core.access.Right;
import core.access.Write;
import core.cwm.COI;
import core.cwm.COISet;
import core.cwm.CompanyDataSet;
import core.cwm.CompanyRecord;
import core.cwm.PRObject;
import core.cwm.PRRecord;
import core.cwm.ValidatorMgr;
import core.obj.Invoice;
import core.obj.Object;
import core.obj.Paycheck;
import core.obj.Payment;
import core.obj.Resume;
import core.obj.StaffingReq;
import core.obj.TimeReport;
import core.sub.Company;
import core.sub.Contractor;
import core.sub.ContractorAsEmployee;
import core.sub.Employee;
import core.sub.Subject;

public class AccessController {
	private static AccessController accessController = null;
	
	private AccessController() {
	}
	
	public static AccessController getInstance() {
		if(accessController == null)
			accessController = new AccessController();
		
		return accessController;
	}
	
	/**
	 * This method allow to check whether a person (Contractor/Employee) can apply for Company
	 * @param sub
	 * @param company
	 * @return
	 */
	public Message apply(Subject sub, Company company) {
		Message msg = null;
		boolean allowed = true;
		Resume resume = null;
		if(sub instanceof Contractor) {
			resume = ((Contractor)sub).getResume();
		} else if(sub instanceof Employee) {
			resume = ((Employee)sub).getResume();
		} else if(sub instanceof ContractorAsEmployee) {
			resume = ((ContractorAsEmployee)sub).getResume();
		} else {
			return new ErrorMessage(ErrorMessage.APPLY_ERROR);
		}
		
		COISet coiSet = COISet.getInstance();
		List<Company> subCompanies = resume.getCompanies();
		
		PRObject prObjects = PRRecord.getInstance().getPRObjects(sub);
		List<Object> objs = prObjects.getObjects();
		if(objs != null) {
			for(Object obj: objs) {
				Company C = CompanyRecord.getInstance().lookup(obj);
				if(!subCompanies.contains(C)) {
					subCompanies.add(C);
				}
			}
		}
		
		List<COI> companyCOISet = coiSet.getCOIs(company);
		List<COI> C_Set = new ArrayList<COI>();
		for(Company C:subCompanies) {
			C_Set.addAll(coiSet.getCOIs(C));
		}
		
		for(int j=0;j<companyCOISet.size();j++) {
			if(C_Set.contains(companyCOISet.get(j))) {
				allowed  = false;
				break;
			}
		}
		if(allowed) {
			msg = new InfoMessage();
			msg.setContent(sub.getID()+ " is allowed to apply for " + company.getID());
		} else {
			msg = new ErrorMessage(ErrorMessage.APPLY_ERROR);
			msg.setContent(sub.getID() + " " + company.getID());
		}
		
		return msg;
	}
	
	/**
	 * This method allows View classes to call
	 * will determine whether a subject has accessed to an object
	 * and define consequences.
	 * @param sub
	 * @param obj
	 * @param right
	 */
	public Message access(Subject sub, Object obj, Right right) {
		ValidatorMgr validatorMgr = ValidatorMgr.getInstance();
		boolean allowed = false;
		Message msg = null;
		if(right instanceof Read) {
			if(obj.isCompanyData()) {
				allowed = validatorMgr.getCWMSSCondValidator().validate(sub, obj, right);
			} else {
				allowed = validatorMgr.getConstraintCondValidator().validate(sub, obj, right);
			}
			
			if(allowed) {
				msg = new ObjectMessage();
				msg.setContent(readObject(obj));
				addSubAccessObj(sub, obj);
				
			} else {
				msg = new ErrorMessage(ErrorMessage.READ_ERROR);
				msg.setContent(sub.getID() + " " + obj.getID());
			}			
		} else if(right instanceof Write) {
			if(obj.isCompanyData()) {
				allowed = validatorMgr.getCWMStarCondValidator().validate(sub, obj, right);
			} else {
				allowed = validatorMgr.getConstraintCondValidator().validate(sub, obj, right);
			}
			
			if(allowed) {
				msg = new ObjectMessage();
				addSubAccessObj(sub, obj);
				
			} else {
				msg = new ErrorMessage(ErrorMessage.WRITE_ERROR);
				msg.setContent(sub.getID() + " " + obj.getID());
			}			
		}

		return msg;
	}
	
	public String readObject(Object obj) {
		String str = null;
		DbMgr dbMgr = DbMgr.getInstance();
		String tableName = dbMgr.getDbname() + "." + Constants.OBJECT_TABLE;
		String sqlQuery = "SELECT content FROM "+ tableName + " WHERE id='"+obj.getID()+"'";
		Connection connection = dbMgr.createConnection();
		ResultSet rs = dbMgr.executeQuery(connection, sqlQuery, DbQuery.SELECT_QUERY);
		try {
			if(rs.next()) {
				str = rs.getString(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		dbMgr.closeConnection(connection);
		return str;
	}
	
	public void loadSubjects(int subType) {
		DbMgr dbMgr = DbMgr.getInstance();
		String tableName = dbMgr.getDbname() + "." + Constants.SUBJECT_TABLE;
		String sqlQuery = "SELECT id FROM "+ tableName + " WHERE type="+subType;
		
		Connection connection = dbMgr.createConnection();
		ResultSet rs = dbMgr.executeQuery(connection, sqlQuery, DbQuery.SELECT_QUERY);
		try {
			while(rs.next()) {
				rs.getString(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		dbMgr.closeConnection(connection);
	}
	
	public void writeObject(Object obj, String str) {
		// Update in-memory object
		obj.setInfo(str);
		
		DbMgr dbMgr = DbMgr.getInstance();
		String tableName = dbMgr.getDbname() + "." + Constants.OBJECT_TABLE;
		String sqlQuery = "UPDATE "+tableName+" SET content='"+ str + "' WHERE id='"+obj.getID()+"'";
		Connection connection = dbMgr.createConnection();
		dbMgr.executeQuery(connection, sqlQuery, DbQuery.UPDATE_QUERY);
		dbMgr.closeConnection(connection);
	}
	
	public void addSubject(Subject sub) {
		// Add to PRRecord
		PRRecord.getInstance().setPRObjects(sub, new PRObject());
		
		DbMgr dbMgr = DbMgr.getInstance();
		String tableName = dbMgr.getDbname() + "." + Constants.SUBJECT_TABLE;
		String sqlQuery = "INSERT INTO "+tableName+"(id,type) VALUES ('"+ sub.getID() + "'," + CommonUtils.getSubjectType(sub)+")" ;
		Connection connection = dbMgr.createConnection();
		dbMgr.executeQuery(connection, sqlQuery, DbQuery.UPDATE_QUERY);
		dbMgr.closeConnection(connection);
		
	}
	
	public void addCOI(COI coi) {
		COISet.getInstance().addCOI(coi);
		
		DbMgr dbMgr = DbMgr.getInstance();
		String tableName = dbMgr.getDbname() + "." + Constants.COI_TABLE;
		String sqlQuery = "INSERT INTO "+tableName+"(coiID,compID) VALUES ('"+ coi.getName() + "','" ;
		Connection connection = dbMgr.createConnection();
		for(Company C:coi.getCompanies()) {
			dbMgr.executeQuery(connection, sqlQuery+C.getID()+"')", DbQuery.UPDATE_QUERY);
		}
		
		dbMgr.closeConnection(connection);
	}
	
	public Subject getSubject(String subID) {
		Subject sub = null;
		int subType = -1;
		DbMgr dbMgr = DbMgr.getInstance();
		String tableName = dbMgr.getDbname() + "." + Constants.SUBJECT_TABLE;
		String sqlQuery = "SELECT type FROM "+ tableName + " WHERE id='"+subID+"'";
		Connection connection = dbMgr.createConnection();
		ResultSet rs = dbMgr.executeQuery(connection, sqlQuery, DbQuery.SELECT_QUERY);
		try {
			if(rs.next()) {
				subType = rs.getInt(1);
				if(subType == Constants.COMPANY_SUB) {
					sub = CompanyRecord.getInstance().lookup(subID);
				} else if(subType == Constants.EMPLOYEE_SUB ||
						subType == Constants.CONTRACTOR_SUB ||
						subType == Constants.EMPLOYEE_CONTRACTOR_SUB) {
					sub = PRRecord.getInstance().lookup(subID);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		dbMgr.closeConnection(connection);
		
		return sub;
	}
	
	public Company getCompany(String companyID) {
		Company sub = null;
		int subType = -1;
		DbMgr dbMgr = DbMgr.getInstance();
		String tableName = dbMgr.getDbname() + "." + Constants.SUBJECT_TABLE;
		String sqlQuery = "SELECT type FROM "+ tableName + " WHERE id='"+companyID+"'";
		Connection connection = dbMgr.createConnection();
		ResultSet rs = dbMgr.executeQuery(connection, sqlQuery, DbQuery.SELECT_QUERY);
		try {
			if(rs.next()) {
				subType = rs.getInt(1);
				if(subType == Constants.COMPANY_SUB) {
					sub = new Company();
					sub.setID(companyID);
				}
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		dbMgr.closeConnection(connection);
		
		return sub;
	}	
	
	public String showSubject2s(int subType) {
		String result = "";
		for(Subject sub:PRRecord.getInstance().getAllSubjects()) {
			if(subType == Constants.COMPANY_SUB) {
				if(sub instanceof Company) {
					result += sub.getID() + "\n";
				}
			} else if(subType == Constants.EMPLOYEE_SUB) {
				if(sub instanceof Employee) {
					result += sub.getID() + "\n";
				}				
			} else if(subType == Constants.CONTRACTOR_SUB) {
				if(sub instanceof Contractor) {
					result += sub.getID() + "\n";
				}				
			} else if(subType == Constants.EMPLOYEE_CONTRACTOR_SUB) {
				if(sub instanceof ContractorAsEmployee) {
					result += sub.getID() + "\n";
				}				
			}
		}
		
		return result;
	}
	
	// Show all COIs
	public String showCOIs() {
		String result = "";
		DbMgr dbMgr = DbMgr.getInstance();
		String tableName = dbMgr.getDbname() + "." + Constants.COI_TABLE;
		String sqlQuery = "SELECT DISTINCT coiID FROM "+ tableName;
		
		Connection connection = dbMgr.createConnection();
		ResultSet rs = dbMgr.executeQuery(connection, sqlQuery, DbQuery.SELECT_QUERY);
		try {
			while(rs.next()) {
				result += rs.getString(1) + "\n";
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		dbMgr.closeConnection(connection);
		return result;
	}
	
	public String showSubjects(int subType) {
		String result = "";
		DbMgr dbMgr = DbMgr.getInstance();
		String tableName = dbMgr.getDbname() + "." + Constants.SUBJECT_TABLE;
		String sqlQuery = "SELECT id FROM "+ tableName + " WHERE type="+subType;
		
		Connection connection = dbMgr.createConnection();
		ResultSet rs = dbMgr.executeQuery(connection, sqlQuery, DbQuery.SELECT_QUERY);
		try {
			while(rs.next()) {
				result += rs.getString(1) + "\n";
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		dbMgr.closeConnection(connection);
		
		return result;
	}	
	
	public String showObjects(int objType) {
		String result = "";
		DbMgr dbMgr = DbMgr.getInstance();
		String tableName = dbMgr.getDbname() + "." + Constants.OBJECT_TABLE;
		String sqlQuery = "SELECT id FROM "+ tableName + " WHERE type="+objType;
		
		Connection connection = dbMgr.createConnection();
		ResultSet rs = dbMgr.executeQuery(connection, sqlQuery, DbQuery.SELECT_QUERY);
		try {
			while(rs.next()) {
				result += rs.getString(1) + "\n";
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		dbMgr.closeConnection(connection);
		
		return result;
	}
	
	public void addObject(Subject sub, Object obj) {
		if(obj instanceof StaffingReq) {
			if(sub instanceof Company) {
				Company C = (Company)sub;
				CompanyRecord.getInstance().getCompanyDataSet(C).addStaffReq((StaffingReq)obj);
			} else {
				// Should not allow
			}
		} else if(obj instanceof TimeReport) {
			if(sub instanceof Company) {
				Company C = (Company)sub;
				CompanyRecord.getInstance().getCompanyDataSet(C).addTimeReport((TimeReport)obj);
			} else {
				// Should not allow
			}			
		} else if(obj instanceof Invoice) {
			if(sub instanceof Company) {
				Company C = (Company)sub;
				CompanyRecord.getInstance().getCompanyDataSet(C).addInvoice((Invoice)obj);
			} else {
				// Should not allow
			}			
		} else if(obj instanceof Payment) {
			if(sub instanceof Company) {
				Company C = (Company)sub;
				CompanyRecord.getInstance().getCompanyDataSet(C).addPayment((Payment)obj);
			} else {
				// Should not allow
			}			
		} else if(obj instanceof Paycheck) {
			if(sub instanceof Contractor) {
				Contractor contractor = (Contractor)sub;
				contractor.addPaycheck((Paycheck)obj);
			} else {
				// Should not allow
			}			
		}
		
		DbMgr dbMgr = DbMgr.getInstance();
		String tableName = dbMgr.getDbname() + "." + Constants.OBJECT_TABLE;
		String sqlQuery = "INSERT INTO "+tableName+"(id,subID,type,content) VALUES ('"+ obj.getID() + "','" + sub.getID() + "'," + CommonUtils.getObjectType(obj)+",'')" ;
		Connection connection = dbMgr.createConnection();
		dbMgr.executeQuery(connection, sqlQuery, DbQuery.UPDATE_QUERY);
		dbMgr.closeConnection(connection);		
	}
	
	public Object findObject(String objID) {
		Object obj = null;
		int objType = -1;
		DbMgr dbMgr = DbMgr.getInstance();
		String tableName = dbMgr.getDbname() + "." + Constants.OBJECT_TABLE;
		String sqlQuery = "SELECT type FROM "+ tableName + " WHERE id='"+objID+"'";
		Connection connection = dbMgr.createConnection();
		ResultSet rs = dbMgr.executeQuery(connection, sqlQuery, DbQuery.SELECT_QUERY);
		try {
			if(rs.next()) {
				objType = rs.getInt(1);
				if(objType == Constants.RESUME_OBJ || objType == Constants.PAYCHECK_OBJ) {
					obj = PRRecord.getInstance().findObject(objID);
				} else if(objType == Constants.STAFF_REQ_OBJ ||
						objType == Constants.TIME_REPORT_OBJ ||
						objType == Constants.INVOICE_OBJ ||
						objType == Constants.PAYMENT_OBJ) {
					obj = CompanyRecord.getInstance().findObject(objID);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		dbMgr.closeConnection(connection);
		
		return obj;
	}	
	
	public void addSubAccessObj(Subject sub, Object obj) {
		if(sub instanceof Company)
			return;
		// Put that object to the PR(sub)
		PRRecord prRecord = PRRecord.getInstance();
		if(prRecord.getPRObjects(sub).add(obj)) {
			// Save to ACCESS table
			DbMgr dbMgr = DbMgr.getInstance();
			String tableName = dbMgr.getDbname() + "." + Constants.ACCESS_TABLE;
			String sqlQuery = "INSERT INTO "+tableName+"(subID,objID) VALUES ('"+ sub.getID() + "','" + obj.getID()+"')" ;
			Connection connection = dbMgr.createConnection();
			dbMgr.executeQuery(connection, sqlQuery, DbQuery.UPDATE_QUERY);
			dbMgr.closeConnection(connection);
		}
	}
	
	public void removeSubAccessObjFromDb(Subject sub, Object obj) {
		// Save to ACCESS table
		DbMgr dbMgr = DbMgr.getInstance();
		String tableName = dbMgr.getDbname() + "." + Constants.ACCESS_TABLE;
		String sqlQuery = "DELETE FROM "+tableName+" WHERE subID='"+ sub.getID() + "' AND objID='" + obj.getID()+"'" ;
		Connection connection = dbMgr.createConnection();
		dbMgr.executeQuery(connection, sqlQuery, DbQuery.DELETE_QUERY);
		dbMgr.closeConnection(connection);
	}
	
	public void addSubWorkForCompany(Subject sub, Company company) {
		DbMgr dbMgr = DbMgr.getInstance();
		String tableName = dbMgr.getDbname() + "." + Constants.OBJECT_TABLE;
		String sqlQuery = null;
		Connection connection = dbMgr.createConnection();
		Resume rs = null;
		int since = 1;
		if(sub instanceof Contractor) {
			Contractor contractor = (Contractor) sub;
			if(contractor.getResume() == null) {
				rs = new Resume();
				rs.setID(Constants.RESUME_INIT+contractor.getID());
				rs.addCompany(company);
				contractor.setResume(rs);
				sqlQuery = "INSERT INTO "+tableName+"(id,subID,type,content) VALUES ('"+ rs.getID()+"','"+sub.getID()+"',"+Constants.RESUME_OBJ+",'" + rs.showCompanies()+"')" ;
			} else {
				rs = contractor.getResume();
				rs.addCompany(company);
				since = rs.getCompanies().size();
				sqlQuery = "UPDATE "+tableName+" SET content ='"+ rs.showCompanies()+"' WHERE id='"+rs.getID()+"'" ;
			}
			dbMgr.executeQuery(connection, sqlQuery, DbQuery.UPDATE_QUERY);
		} else if(sub instanceof Employee) {
			Employee employee = (Employee) sub;
			if(employee.getResume() == null) {
				rs = new Resume();
				rs.setID(Constants.RESUME_INIT+employee.getID());
				rs.addCompany(company);
				employee.setResume(rs);
				sqlQuery = "INSERT INTO "+tableName+"(id,subID,type,content) VALUES ('"+ rs.getID()+"','"+sub.getID()+"',"+Constants.RESUME_OBJ+",'" + rs.showCompanies()+"')" ;
			} else {
				rs = employee.getResume();
				rs.addCompany(company);
				since = rs.getCompanies().size();
				sqlQuery = "UPDATE "+tableName+" SET content ='"+ rs.showCompanies()+"' WHERE id='"+rs.getID()+"'" ;
			}
			dbMgr.executeQuery(connection, sqlQuery, DbQuery.UPDATE_QUERY);			
		} else if(sub instanceof ContractorAsEmployee) {
			ContractorAsEmployee employee = (ContractorAsEmployee) sub;
			if(employee.getResume() == null) {
				rs = new Resume();
				rs.setID(Constants.RESUME_INIT+employee.getID());
				rs.addCompany(company);
				employee.setResume(rs);
				sqlQuery = "INSERT INTO "+tableName+"(id,subID,type,content) VALUES ('"+ rs.getID()+"','"+sub.getID()+"',"+Constants.RESUME_OBJ+",'" + rs.showCompanies()+"')" ;
			} else {
				rs = employee.getResume();
				rs.addCompany(company);
				since = rs.getCompanies().size();
				sqlQuery = "UPDATE "+tableName+" SET content ='"+ rs.showCompanies()+"' WHERE id='"+rs.getID()+"'" ;
			}
			dbMgr.executeQuery(connection, sqlQuery, DbQuery.UPDATE_QUERY);
		} else {
			// Should not allow
			return;
		}
		
		tableName = dbMgr.getDbname() + "." + Constants.COMPANY_TABLE;
		sqlQuery = "INSERT INTO "+tableName+"(subID,compID,since) VALUES ('"+ sub.getID() + "','" + company.getID()+"',"+since+")" ;
		dbMgr.executeQuery(connection, sqlQuery, DbQuery.UPDATE_QUERY);
		dbMgr.closeConnection(connection);		
	}
	
	public void addCompanyData(Company company, CompanyDataSet data) {
		List<Object> objects = data.getAllData();
		for(Object obj:objects) {
			addObject(company, obj);
		}
	}
	
	public void initialize() {
		// Load subjects + objects from Database into memory
		loadSubjects();
		loadObjects();
	}
	
	private void loadSubjects() {
		DbMgr dbMgr = DbMgr.getInstance();
		String tableName = dbMgr.getDbname() + "." + Constants.SUBJECT_TABLE;
		String sqlQuery = null;
		ResultSet rs = null;
		Connection connection = dbMgr.createConnection();
		String id;
		int type;
		
		// Load data from Subject table
		sqlQuery = "SELECT id,type FROM "+ tableName;
		rs = dbMgr.executeQuery(connection, sqlQuery, DbQuery.SELECT_QUERY);
		try {
			while(rs.next()) {
				id = rs.getString(1);
				type = rs.getInt(2);
				if(type == Constants.COMPANY_SUB) {
					Company company = new Company();
					company.setID(id);
					CompanyRecord.getInstance().addCompanyDataSet(company, new CompanyDataSet());
				} else if(type == Constants.EMPLOYEE_SUB) {
					Employee employee = new Employee();
					employee.setID(id);
					PRRecord.getInstance().setPRObjects(employee, new PRObject());
				} else if(type == Constants.CONTRACTOR_SUB) {
					Contractor contractor = new Contractor();
					contractor.setID(id);
					PRRecord.getInstance().setPRObjects(contractor, new PRObject());
				} else if(type == Constants.EMPLOYEE_CONTRACTOR_SUB) {
					ContractorAsEmployee ce = new ContractorAsEmployee();
					ce.setID(id);
					PRRecord.getInstance().setPRObjects(ce, new PRObject());
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		// Load COIs
		String coiID, compID;
		COI coi = null;
		tableName = dbMgr.getDbname() + "." + Constants.COI_TABLE;
		sqlQuery = "SELECT coiID,compID FROM "+ tableName;
		rs = dbMgr.executeQuery(connection, sqlQuery, DbQuery.SELECT_QUERY);
		try {
			while(rs.next()) {
				coiID = rs.getString(1);
				compID = rs.getString(2);
				coi = COISet.getInstance().addCOI(coiID);
				Company company = CompanyRecord.getInstance().lookup(compID);
				coi.add(company);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		dbMgr.closeConnection(connection);
		
	}
	
	private void loadObjects() {
		DbMgr dbMgr = DbMgr.getInstance();
		String tableName = dbMgr.getDbname() + "." + Constants.OBJECT_TABLE;
		String sqlQuery = null;
		ResultSet rs = null;
		Connection connection = dbMgr.createConnection();
		String id, subID,content;
		int type;
		
		// Load data from Object table
		sqlQuery = "SELECT id,subID,type,content FROM "+ tableName;
		rs = dbMgr.executeQuery(connection, sqlQuery, DbQuery.SELECT_QUERY);
		try {
			while(rs.next()) {
				id = rs.getString(1);
				subID = rs.getString(2);
				type = rs.getInt(3);
				content = rs.getString(4);
				if(type == Constants.INVOICE_OBJ) {
					Invoice obj = new Invoice();
					obj.setID(id);
					obj.setInfo(content);
					Company company = CompanyRecord.getInstance().lookup(subID);
					CompanyRecord.getInstance().getCompanyDataSet(company).addInvoice(obj);
				} else if(type == Constants.PAYCHECK_OBJ) {
					Paycheck obj = new Paycheck();
					obj.setID(id);
					obj.setInfo(content);
					Subject sub = PRRecord.getInstance().lookup(subID);
					if(sub instanceof Contractor) {
						((Contractor)sub).addPaycheck(obj);
					} else if(sub instanceof ContractorAsEmployee) {
						((ContractorAsEmployee)sub).addPaycheck(obj);
					} 
					
				} else if(type == Constants.PAYMENT_OBJ) {
					Payment obj = new Payment();
					obj.setID(id);
					obj.setInfo(content);
					Company company = CompanyRecord.getInstance().lookup(subID);
					CompanyRecord.getInstance().getCompanyDataSet(company).addPayment(obj);
					
				} else if(type == Constants.RESUME_OBJ) {
					Resume obj = new Resume();
					obj.setID(id);
					obj.setInfo(content);
					Subject sub = PRRecord.getInstance().lookup(subID);
					if(sub instanceof Contractor) {
						((Contractor)sub).setResume(obj);
					} else if(sub instanceof ContractorAsEmployee) {
						((ContractorAsEmployee)sub).setResume(obj);
					}else if(sub instanceof Employee) {
						((Employee)sub).setResume(obj);
					}
					
				} else if(type == Constants.STAFF_REQ_OBJ) {
					StaffingReq obj = new StaffingReq(id);
					obj.setInfo(content);
					Company company = CompanyRecord.getInstance().lookup(subID);
					CompanyRecord.getInstance().getCompanyDataSet(company).addStaffReq(obj);
					
				} else if(type == Constants.TIME_REPORT_OBJ) {
					TimeReport obj = new TimeReport(id);
					obj.setInfo(content);
					Company company = CompanyRecord.getInstance().lookup(subID);
					CompanyRecord.getInstance().getCompanyDataSet(company).addTimeReport(obj);
				}
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		// Load data from Company data to make Subject.resume
		String compID;
		Company company = null;
		tableName = dbMgr.getDbname() + "." + Constants.COMPANY_TABLE;
		Set<Subject> subjects = PRRecord.getInstance().getAllSubjects();
		for(Subject sub: subjects) {
			subID = sub.getID();
			sqlQuery = "SELECT compID FROM "+ tableName+ " WHERE subID='"+subID+ "' ORDER BY since ASC";
			rs = dbMgr.executeQuery(connection, sqlQuery, DbQuery.SELECT_QUERY);
			try {
				while(rs.next()) {
					compID = rs.getString(1);
					company = CompanyRecord.getInstance().lookup(compID);
					if(company != null) {
						if(sub instanceof Contractor) {
							((Contractor)sub).getResume().addCompany(company);
						} else if(sub instanceof Employee) {
							((Employee)sub).getResume().addCompany(company);
						} else if(sub instanceof ContractorAsEmployee) {
							((ContractorAsEmployee)sub).getResume().addCompany(company);
						}
					}
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}			
		}
		
		// Load data from Access table
		String objID;
		tableName = dbMgr.getDbname() + "." + Constants.ACCESS_TABLE;
		sqlQuery = "SELECT subID,objID FROM "+ tableName;
		rs = dbMgr.executeQuery(connection, sqlQuery, DbQuery.SELECT_QUERY);
		try {
			while(rs.next()) {
				subID = rs.getString(1);
				objID = rs.getString(2);
				Subject sub = PRRecord.getInstance().lookup(subID);
				if(sub != null) {
					// Find object whose ID = objID
					Object obj = findObject(objID);
					if(obj != null)
						PRRecord.getInstance().getPRObjects(sub).add(obj);
				}
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		dbMgr.closeConnection(connection);
	}
}
